<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>银行管理系统 - 管理员控制台</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.0/font/bootstrap-icons.css">
    <style>
        body {
            background-color: #f8f9fa;
        }
        .sidebar {
            background-color: #2c3e50;
            color: #ecf0f1;
            height: 100vh;
            position: fixed;
            padding-top: 20px;
        }
        .main-content {
            margin-left: 250px;
            padding: 20px;
        }
        .table-card {
            margin-bottom: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        .nav-link {
            color: #ecf0f1;
            padding: 15px 20px;
            border-radius: 0;
        }
        .nav-link:hover, .nav-link.active {
            background-color: #34495e;
            color: #1abc9c;
        }
        .table-responsive {
            max-height: 500px;
            overflow-y: auto;
        }
        .card-header {
            background-color: #3498db;
            color: white;
        }
        .btn-action {
            margin-right: 5px;
        }
        .sql-editor {
            background-color: #f5f5f5;
            border-radius: 5px;
            padding: 15px;
            margin-top: 20px;
        }
        .status-badge {
            cursor: pointer;
        }
    </style>
</head>
<body>
    <div class="container-fluid">
        <div class="row">
            <!-- 侧边导航栏 -->
            <div class="col-md-3 col-lg-2 d-md-block sidebar collapse">
                <div class="position-sticky">
                    <h4 class="text-center mb-4"><i class="bi bi-bank"></i> 安心银行管理系统</h4>
                    <ul class="nav flex-column">
                        <li class="nav-item">
                            <a class="nav-link active" href="#" data-table="userinfo">
                                <i class="bi bi-people"></i> 用户信息
                            </a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="#" data-table="cardinfo">
                                <i class="bi bi-credit-card"></i> 银行卡信息
                            </a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="#" data-table="tradeinfo">
                                <i class="bi bi-cash-coin"></i> 交易记录
                            </a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="#" data-table="audit_log">
                                <i class="bi bi-journal-text"></i> 审计日志
                            </a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link" href="#" data-table="deposit">
                                <i class="bi bi-piggy-bank"></i> 存款类型
                            </a>
                        </li>
                        <li class="nav-item mt-4">
                            <a class="nav-link" href="#" data-table="sql">
                                <i class="bi bi-code-slash"></i> SQL查询
                            </a>
                        </li>
                    </ul>
                </div>
            </div>

            <!-- 主内容区 -->
            <div class="col-md-9 ms-sm-auto col-lg-10 px-md-4 main-content">
                <div class="d-flex justify-content-between flex-wrap flex-md-nowrap align-items-center pt-3 pb-2 mb-3 border-bottom">
                    <h1 class="h2">银行管理系统 - 管理员控制台</h1>
                    <div class="btn-toolbar mb-2 mb-md-0">
                        <div class="btn-group me-2">
                            <button class="btn btn-sm btn-outline-secondary" id="refresh-btn">
                                <i class="bi bi-arrow-repeat"></i> 刷新数据
                            </button>
                        </div>
                    </div>
                </div>

                <!-- 数据表格区域 -->
                <div id="table-container">
                    <div class="card table-card">
                        <div class="card-header d-flex justify-content-between align-items-center">
                            <h5 class="mb-0">用户信息</h5>
                            <button class="btn btn-sm btn-light" id="add-user-btn">
                                <i class="bi bi-plus-lg"></i> 添加用户
                            </button>
                        </div>
                        <div class="card-body">
                            <div class="table-responsive">
                                <table class="table table-hover" id="data-table">
                                    <thead>
                                        <tr>
                                            <th>ID</th>
                                            <th>姓名</th>
                                            <th>身份证号</th>
                                            <th>电话</th>
                                            <th>地址</th>
                                            <th>操作</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <!-- 数据将通过JavaScript填充 -->
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- SQL查询区域 -->
                <div id="sql-container" class="d-none">
                    <div class="card table-card">
                        <div class="card-header">
                            <h5 class="mb-0">SQL查询</h5>
                        </div>
                        <div class="card-body">
                            <div class="mb-3">
                                <textarea class="form-control" id="sql-query" rows="4"
                                          placeholder="输入SQL查询语句，例如：SELECT * FROM userinfo"></textarea>
                            </div>
                            <button class="btn btn-primary" id="execute-sql">执行查询</button>
                            <div class="table-responsive mt-3">
                                <table class="table table-hover" id="sql-result-table">
                                    <thead>
                                        <tr>
                                            <!-- 表头将根据查询结果动态生成 -->
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <!-- 查询结果将通过JavaScript填充 -->
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- 添加/编辑用户模态框 -->
    <div class="modal fade" id="userModal" tabindex="-1" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="modalTitle">添加新用户</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <form id="userForm">
                        <input type="hidden" id="customerID">
                        <div class="mb-3">
                            <label for="customerName" class="form-label">姓名</label>
                            <input type="text" class="form-control" id="customerName" required>
                        </div>
                        <div class="mb-3">
                            <label for="PID" class="form-label">身份证号</label>
                            <input type="text" class="form-control" id="PID" required>
                        </div>
                        <div class="mb-3">
                            <label for="telephone" class="form-label">电话</label>
                            <input type="text" class="form-control" id="telephone" required>
                        </div>
                        <div class="mb-3">
                            <label for="address" class="form-label">地址</label>
                            <input type="text" class="form-control" id="address" required>
                        </div>
                        <div class="mb-3">
                            <label for="password" class="form-label">密码</label>
                            <input type="password" class="form-control" id="password" required>
                        </div>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">取消</button>
                    <button type="button" class="btn btn-primary" id="saveUser">保存</button>
                </div>
            </div>
        </div>
    </div>

    <!-- 添加/编辑银行卡模态框 -->
    <div class="modal fade" id="cardModal" tabindex="-1" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="cardModalTitle">添加银行卡</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <form id="cardForm">
                        <input type="hidden" id="cardID">
                        <div class="mb-3">
                            <label for="customerIDCard" class="form-label">用户</label>
                            <select class="form-select" id="customerIDCard" required>
                                <option value="">选择用户</option>
                                <!-- 用户选项将通过JavaScript填充 -->
                            </select>
                        </div>
                        <div class="mb-3">
                            <label for="curID" class="form-label">货币类型</label>
                            <select class="form-select" id="curID">
                                <option value="CNY">人民币 (CNY)</option>
                                <option value="USD">美元 (USD)</option>
                                <option value="EUR">欧元 (EUR)</option>
                                <option value="JPY">日元 (JPY)</option>
                            </select>
                        </div>
                        <div class="mb-3">
                            <label for="savingID" class="form-label">存款类型</label>
                            <select class="form-select" id="savingID">
                                <option value="1">活期存款</option>
                                <option value="2">定期存款</option>
                                <option value="3">零存整取</option>
                            </select>
                        </div>
                        <div class="mb-3">
                            <label for="openMoney" class="form-label">开户金额</label>
                            <input type="number" class="form-control" id="openMoney" value="10.00" step="0.01" min="10" required>
                        </div>
                        <div class="mb-3">
                            <label for="pass" class="form-label">密码 (6位数字)</label>
                            <input type="text" class="form-control" id="pass" maxlength="6" required>
                        </div>
                    </form>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">取消</button>
                    <button type="button" class="btn btn-primary" id="saveCard">保存</button>
                </div>
            </div>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        $(document).ready(function() {
            let currentTable = 'userinfo';

            // 初始化加载数据
            loadTableData(currentTable);
            loadUsersForCardModal();

            // 导航菜单点击事件
            $('.nav-link').on('click', function(e) {
                e.preventDefault();

                // 更新活动状态
                $('.nav-link').removeClass('active');
                $(this).addClass('active');

                // 获取要显示的表
                currentTable = $(this).data('table');

                if (currentTable === 'sql') {
                    $('#table-container').addClass('d-none');
                    $('#sql-container').removeClass('d-none');
                } else {
                    $('#sql-container').addClass('d-none');
                    $('#table-container').removeClass('d-none');
                    updateTableTitle(currentTable);
                    loadTableData(currentTable);
                }
            });

            // 刷新按钮点击事件
            $('#refresh-btn').on('click', function() {
                if (currentTable === 'sql') {
                    $('#sql-query').val('');
                    $('#sql-result-table thead, #sql-result-table tbody').empty();
                } else {
                    loadTableData(currentTable);
                }
            });

            // 添加用户按钮点击事件
            $('#add-user-btn').on('click', function() {
                $('#userForm')[0].reset();
                $('#modalTitle').text('添加新用户');
                $('#customerID').val('');
                $('#userModal').modal('show');
            });

            // 保存用户
            $('#saveUser').on('click', function() {
                const userId = parseInt($('#customerID').val());
                const userData = {
                    customerName: $('#customerName').val(),
                    PID: $('#PID').val(),
                    telephone: $('#telephone').val(),
                    address: $('#address').val(),
                    password: $('#password').val()
                };

                const url = userId ?
                    `/api/tables/userinfo/${userId}` :
                    '/api/tables/userinfo';

                const method = userId ? 'PUT' : 'POST';

                $.ajax({
                    url: url,
                    type: method,
                    contentType: 'application/json',
                    data: JSON.stringify(userData),
                    success: function(response) {
                        $('#userModal').modal('hide');
                        loadTableData('userinfo');
                        loadUsersForCardModal(); // 刷新用户下拉列表
                        showAlert('操作成功!', 'success');
                    },
                    error: function(xhr) {
                        showAlert('操作失败: ' + xhr.responseJSON.error, 'danger');
                    }
                });
            });

            // 执行SQL查询
            $('#execute-sql').on('click', function() {
                const sql = $('#sql-query').val().trim();
                if (!sql) {
                    showAlert('请输入SQL查询语句', 'warning');
                    return;
                }

                $.ajax({
                    url: '/api/query',
                    type: 'POST',
                    contentType: 'application/json',
                    data: JSON.stringify({ sql }),
                    success: function(data) {
                        displaySqlResults(data);
                    },
                    error: function(xhr) {
                        showAlert('查询失败: ' + xhr.responseJSON.error, 'danger');
                    }
                });
            });

            // 更新表格标题
            function updateTableTitle(tableName) {
                const titles = {
                    'userinfo': '用户信息',
                    'cardinfo': '银行卡信息',
                    'tradeinfo': '交易记录',
                    'audit_log': '审计日志',
                    'deposit': '存款类型'
                };

                const buttonHTML = {
                    'userinfo': '<button class="btn btn-sm btn-light" id="add-user-btn"><i class="bi bi-plus-lg"></i> 添加用户</button>',
                    'cardinfo': '<button class="btn btn-sm btn-light" id="add-card-btn"><i class="bi bi-plus-lg"></i> 添加银行卡</button>',
                    'tradeinfo': '',
                    'audit_log': '',
                    'deposit': ''
                };

                $('.card-header h5').text(titles[tableName]);
                $('.card-header').find('button').remove();
                $('.card-header').append(buttonHTML[tableName]);

                if (tableName === 'cardinfo') {
                    $('#add-card-btn').on('click', function() {
                        $('#cardForm')[0].reset();
                        $('#cardModalTitle').text('添加银行卡');
                        $('#cardID').val('');
                        $('#cardModal').modal('show');
                    });
                }
            }

            // 加载表格数据
            function loadTableData(tableName) {
                $.get(`/api/tables/${tableName}`, function(data) {
                    const $table = $('#data-table');
                    $table.find('tbody').empty();

                    // 根据表名更新表头
                    updateTableHeader(tableName, $table);

                    // 填充数据
                    data.forEach(row => {
                        const $tr = $('<tr>');

                        // 根据表名生成行数据
                        generateTableRow(tableName, row, $tr);

                        $table.find('tbody').append($tr);
                    });
                }).fail(function(xhr) {
                    showAlert('加载数据失败: ' + xhr.responseJSON.error, 'danger');
                });
            }

            // 更新表头
            function updateTableHeader(tableName, $table) {
                const headers = {
                    'userinfo': ['ID', '姓名', '身份证号', '电话', '地址', '操作'],
                    'cardinfo': ['卡号', '货币类型', '存款类型', '开户金额', '余额', '状态', '用户ID', '操作'],
                    'tradeinfo': ['交易时间', '交易类型', '卡号', '交易金额', '备注'],
                    'audit_log': ['ID', '卡号', '操作时间', '操作类型', '备注'],
                    'deposit': ['存款类型ID', '存款类型名称', '描述']
                };

                const $thead = $table.find('thead');
                $thead.empty();

                const $headerRow = $('<tr>');
                headers[tableName].forEach(header => {
                    $headerRow.append($('<th>').text(header));
                });

                $thead.append($headerRow);
            }

            // 生成表格行
            function generateTableRow(tableName, row, $tr) {
                switch(tableName) {
                    case 'userinfo':
                        $tr.append(
                            $('<td>').text(row.customerID),
                            $('<td>').text(row.customerName),
                            $('<td>').text(row.PID),
                            $('<td>').text(row.telephone),
                            $('<td>').text(row.address),
                            $('<td>').html(`
                                <button class="btn btn-sm btn-primary btn-action edit-user"
                                    data-id="${row.customerID}">
                                    <i class="bi bi-pencil"></i> 编辑
                                </button>
                                <button class="btn btn-sm btn-danger btn-action delete-user"
                                    data-id="${row.customerID}">
                                    <i class="bi bi-trash"></i> 删除
                                </button>
                            `)
                        );
                        break;

                    case 'cardinfo':
                        $tr.append(
                            $('<td>').text(row.cardID),
                            $('<td>').text(row.curID),
                            $('<td>').text(getSavingType(row.savingID)),
                            $('<td>').text('¥' + row.openMoney),
                            $('<td>').text('¥' + row.balance),
                            $('<td>').html(row.IsReportLoss === '0' ?
                                '<span class="badge bg-success status-badge" data-id="'+row.cardID+'" data-status="0">正常</span>' :
                                '<span class="badge bg-danger status-badge" data-id="'+row.cardID+'" data-status="1">挂失</span>'),
                            $('<td>').text(row.customerID),
                            $('<td>').html(`
                                <button class="btn btn-sm btn-primary btn-action edit-card"
                                    data-id="${row.cardID}">
                                    <i class="bi bi-pencil"></i> 编辑
                                </button>
                                <button class="btn btn-sm btn-danger btn-action delete-card"
                                    data-id="${row.cardID}">
                                    <i class="bi bi-trash"></i> 删除
                                </button>
                                <button class="btn btn-sm ${row.IsReportLoss === '0' ? 'btn-warning' : 'btn-success'} btn-action toggle-loss"
                                    data-id="${row.cardID}" data-status="${row.IsReportLoss}">
                                    ${row.IsReportLoss === '0' ? '<i class="bi bi-exclamation-triangle"></i> 挂失' : '<i class="bi bi-check-circle"></i> 解挂'}
                                </button>
                            `)
                        );
                        break;

                    case 'tradeinfo':
                        $tr.append(
                            $('<td>').text(new Date(row.tradeDate).toLocaleString()),
                            $('<td>').text(row.tradeType),
                            $('<td>').text(row.cardID),
                            $('<td>').text('¥' + row.tradeMoney),
                            $('<td>').text(row.remark)
                        );
                        break;

                    case 'audit_log':
                        $tr.append(
                            $('<td>').text(row.id),
                            $('<td>').text(row.card_id),
                            $('<td>').text(new Date(row.operation_time).toLocaleString()),
                            $('<td>').text(row.operation_type),
                            $('<td>').text(row.remark)
                        );
                        break;

                    case 'deposit':
                        $tr.append(
                            $('<td>').text(row.savingID),
                            $('<td>').text(row.savingName),
                            $('<td>').text(row.descrip)
                        );
                        break;
                }
            }

            // 获取存款类型名称
            function getSavingType(id) {
                const types = {
                    1: '活期存款',
                    2: '定期存款',
                    3: '零存整取'
                };
                return types[id] || '未知类型';
            }

            // 编辑用户事件委托
            $(document).on('click', '.edit-user', function() {
                const userId = $(this).data('id');

                $.get(`/api/tables/userinfo`, function(users) {
                    const user = users.find(u => u.customerID == userId);
                    if (user) {
                        $('#customerID').val(user.customerID);
                        $('#customerName').val(user.customerName);
                        $('#PID').val(user.PID);
                        $('#telephone').val(user.telephone);
                        $('#address').val(user.address);
                        $('#password').val(user.password);

                        $('#modalTitle').text('编辑用户');
                        $('#userModal').modal('show');
                    }
                });
            });

            // 删除用户事件委托
            $(document).on('click', '.delete-user', function() {
                const userId = $(this).data('id');

                if (confirm('确定要删除该用户吗？此操作将删除该用户的所有银行卡！')) {
                    $.ajax({
                        url: `/api/tables/userinfo/${userId}`,
                        type: 'DELETE',
                        success: function() {
                            loadTableData('userinfo');
                            loadUsersForCardModal(); // 刷新用户下拉列表
                            showAlert('用户删除成功!', 'success');
                        },
                        error: function(xhr) {
                            showAlert('删除失败: ' + xhr.responseJSON.error, 'danger');
                        }
                    });
                }
            });

            // 编辑银行卡事件委托
            $(document).on('click', '.edit-card', function() {
                const cardId = $(this).data('id');

                $.get(`/api/tables/cardinfo`, function(cards) {
                    const card = cards.find(c => c.cardID == cardId);
                    if (card) {
                        $('#cardID').val(card.cardID);
                        $('#customerIDCard').val(card.customerID);
                        $('#curID').val(card.curID);
                        $('#savingID').val(card.savingID);
                        $('#openMoney').val(card.openMoney);
                        $('#pass').val(card.pass);

                        $('#cardModalTitle').text('编辑银行卡');
                        $('#cardModal').modal('show');
                    }
                });
            });

            // 删除银行卡事件委托
            $(document).on('click', '.delete-card', function() {
                const cardId = $(this).data('id');

                if (confirm('确定要删除该银行卡吗？此操作将删除该卡的所有交易记录！')) {
                    $.ajax({
                        url: `/api/tables/cardinfo/${cardId}`,
                        type: 'DELETE',
                        success: function() {
                            loadTableData('cardinfo');
                            showAlert('银行卡删除成功!', 'success');
                        },
                        error: function(xhr) {
                            showAlert('删除失败: ' + xhr.responseJSON.error, 'danger');
                        }
                    });
                }
            });

            // 挂失/解挂银行卡事件委托
            $(document).on('click', '.toggle-loss', function() {
                const cardId = $(this).data('id');
                const currentStatus = $(this).data('status');

                const action = currentStatus === '0' ? '挂失' : '解挂';
                if (!confirm(`确定要${action}该银行卡吗？`)) {
                    return;
                }

                $.ajax({
                    url: `/api/tables/cardinfo/${cardId}/toggle-loss`,
                    type: 'PUT',
                    success: function(response) {
                        if (response.success) {
                            loadTableData('cardinfo');
                            showAlert(`银行卡${action}成功!`, 'success');
                        }
                    },
                    error: function(xhr) {
                        showAlert(`${action}失败: ` + xhr.responseJSON.error, 'danger');
                    }
                });
            });

            // 保存银行卡
            $('#saveCard').on('click', function() {
                const cardId = $('#cardID').val();
                const cardData = {
                    customerID: $('#customerIDCard').val(),
                    curID: $('#curID').val(),
                    savingID: $('#savingID').val(),
                    openMoney: $('#openMoney').val(),
                    pass: $('#pass').val()
                };

                const url = cardId ?
                    `/api/tables/cardinfo/${cardId}` :
                    '/api/tables/cardinfo';

                const method = cardId ? 'PUT' : 'POST';

                $.ajax({
                    url: url,
                    type: method,
                    contentType: 'application/json',
                    data: JSON.stringify(cardData),
                    success: function(response) {
                        $('#cardModal').modal('hide');
                        loadTableData('cardinfo');
                        showAlert('操作成功!', 'success');
                    },
                    error: function(xhr) {
                        showAlert('操作失败: ' + xhr.responseJSON.error, 'danger');
                    }
                });
            });

            // 加载用户列表到银行卡模态框
            function loadUsersForCardModal() {
                $.get('/api/tables/userinfo', function(users) {
                    const $select = $('#customerIDCard');
                    $select.empty();
                    $select.append('<option value="">选择用户</option>');

                    users.forEach(user => {
                        $select.append(new Option(`${user.customerName} (ID: ${user.customerID})`, user.customerID));
                    });
                });
            }

            // 显示SQL查询结果
            function displaySqlResults(data) {
                const $table = $('#sql-result-table');
                $table.find('thead').empty();
                $table.find('tbody').empty();

                if (data.length === 0) {
                    showAlert('没有查询到结果', 'info');
                    return;
                }

                // 创建表头
                const $headerRow = $('<tr>');
                Object.keys(data[0]).forEach(key => {
                    $headerRow.append($('<th>').text(key));
                });
                $table.find('thead').append($headerRow);

                // 创建数据行
                data.forEach(row => {
                    const $tr = $('<tr>');
                    Object.values(row).forEach(value => {
                        $tr.append($('<td>').text(value));
                    });
                    $table.find('tbody').append($tr);
                });
            }

            // 显示提示信息
            function showAlert(message, type) {
                const $alert = $(`
                    <div class="alert alert-${type} alert-dismissible fade show position-fixed top-0 end-0 m-3" role="alert" style="z-index: 9999;">
                        ${message}
                        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
                    </div>
                `);

                $('body').append($alert);

                // 5秒后自动关闭
                setTimeout(() => {
                    $alert.alert('close');
                }, 5000);
            }
        });
    </script>
</body>
</html>